*------------------------------------------------------------------------------*
		*** CREATE REFORMED COUNTIES FOR PRE-REFORM PERIOD - VOTING ***
*------------------------------------------------------------------------------*

***Sachsen-Anhalt municipalities to different counties
import excel "${rawdata}refromed_counties/votes/2007.xlsx", sheet("Gebietsaenderungen_2007") clear
keep if substr(D,1,5)=="15151" | substr(D,1,5)=="15352"
sort E
drop A B C F G H I L M
destring D J, replace
rename (D J)(Gemeinde_ID Gemeinde_ID_)
save "${tempdata}Sachsen_Anhalt_Gemeinde_to_Different_Kreis.dta", replace

*------------------------------------------------------------------------------*

***Sachsen - 2002 federal elections
import excel "${rawdata}refromed_counties/votes/Sachsen_federal_2002.xlsx", sheet("14111-093M") clear
drop in 9/563 
drop in 6/9
drop in 1/4
drop in 557/563
drop D E 
drop if length(A) < 9
replace A = substr(A, 6,.)
replace A = substr(A, 1,8)
drop if strpos(A , "-")!=0

destring A, replace
rename (B A C F G H I J K)(Gemeinde Gemeinde_ID Eligible_voters Valid CDU SPD DIELINKE GRÜNE FDP)
foreach x of varlist Eligible_voters-U {
destring `x', replace	
}
encode R, gen(R_)
encode S, gen(S_)
encode T, gen(T_)
encode U, gen(U_)
egen int SonstigeParteien = rowtotal(L-Q R_-U_)
drop in 1
drop L-U R_-U_
gen Land_code="14"
gen election_date="22Sep2002"
gen election_typeBWorLW = "BW"
destring Gemeinde_ID, replace
save "${tempdata}S_federal_2002.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern - 2002 state elections
import excel "${rawdata}refromed_counties/votes/Mecklenburg-Vorpommern_state_2002.xls", sheet("B724G 200201") clear
drop in 1/5
drop in 2
drop A
replace B = "Gemeinde_ID" in 1
replace C = "Gemeinde" in 1
replace D = "Eligible_voters" in 1
replace X="Valid" in 1
drop E-W
replace AL = "VPMV" in 1

foreach x of varlist B-AL {
	rename (`x')(`=`x'[1]')
} // x
drop in 1

destring Gemeinde_ID, replace

foreach x of varlist SPD-VPMV {
	destring  `x', replace
} // x

egen int SonstigeParteien = rowtotal(NPD REP GRAUE PBC BMV SPASSPARTEI Schill SLP VPMV)
drop NPD REP GRAUE PBC BMV SPASSPARTEI Schill SLP VPMV
duplicates drop 
drop if Gemeinde_ID==.
drop if substr(Gemeinde,1,5)=="Brief"
rename PDS DIELINKE
gen Land_code="13"
gen election_date="22Sep2002"
destring Eligible_voters Valid, replace
gen election_typeBWorLW = "LW"
save "${tempdata}MV_state_2002.dta", replace

*------------------------------------------------------------------------------*

***Sachsen-Anhalt - 2002 federal elections
import delimited "${rawdata}refromed_counties/votes/Sachsen-Anhalt_federal_2002.csv", delimiter(";") encoding(ISO-8859-2) numericcols(18 19 20 21 22 23) clear 
egen int SonstigeParteien = rowtotal(f06-f08)
drop ergart-sart b c-e f06-f08
rename (ags name a f f01 f02 f03 f04 f05)(Gemeinde_ID Gemeinde Eligible_voters Valid SPD CDU DIELINKE FDP GRÜNE)
drop ags2019 name2019
gen election_date="22Sep2002"
destring Eligible_voters Valid, replace
gen election_typeBWorLW = "BW"
gen Land_code="15"
save "${tempdata}SA_federal_2002.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern - 2002 federal elections
import excel "${rawdata}refromed_counties/votes/Mecklenburg-Vorpommern_federal_2002.xls", sheet("B714G 200201") clear

drop A E-Q
drop in 1/6
drop in 2
replace B = "Gemeinde_ID" in 1
replace C = "Gemeinde" in 1

foreach x of varlist B-Z {
	rename (`x')(`=`x'[1]')
} // x
drop in 1
destring Gemeinde_ID, replace

foreach x of varlist insgesamt-Schill {
	destring  `x', replace
} // x

rename PDS DIELINKE
egen SonstigeParteien = rowtotal(NPD REP Schill)
drop NPD REP Schill
drop in 980/1022
drop if substr(Gemeinde,1,5)=="Brief"
rename (insgesamt gültig)(Eligible_voters Valid)
gen election_typeBWorLW = "BW"
gen Land_code="13"
gen election_date="22Sep2002"
save "${tempdata}MV_federal_2002.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern - 2005 federal elections

import excel "${rawdata}refromed_counties/votes/Mecklenburg-Vorpommern_federal_2005.xls", sheet("Ergebnisse nach Gemeinden") clear
drop in 1/5
drop in 2
drop A B F-R
drop in 853/855
replace C = "Gemeinde_ID" in 1
replace D = "Gemeinde" in 1
replace V = "DIELINKE" in 1
replace E= "E" in 1
foreach x of varlist C-AB {
	rename (`x')(`=`x'[1]')
} // x

drop in 1
destring Gemeinde_ID, replace

foreach x of varlist SPD-PBC {
	destring  `x', replace
} // x

egen int SonstigeParteien = rowtotal(NPD GRAUE MLPD PBC)
drop NPD GRAUE MLPD PBC
drop if substr(Gemeinde,1,5)=="Brief"
rename (E gültig)(Eligible_voters Valid)
destring Eligible_voters Valid, replace
gen election_typeBWorLW = "BW"
gen Land_code="13"
gen election_date="18Sep2005"
save "${tempdata}MV_federal_2005.dta", replace

*------------------------------------------------------------------------------*

***Sachsen - 2004 state elections
import excel "${rawdata}refromed_counties/votes/Sachsen_state_2004.xlsx", sheet("14311-093M") clear
drop in 9/607 
drop in 607/622
drop in 1/4
drop in 2/4
drop if length(A) < 9
replace A = substr(A, 6,.)
replace A = substr(A, 1,8)
destring A, replace

foreach x of varlist A-AF {
	replace `x' = "0" if `x'=="x"
} // x

foreach x of varlist A C-AF {
	destring `x', replace
} // x

egen int SonstigeParteien = rowtotal(K M-AF)
rename (B A C F G H I J L)(Gemeinde Gemeinde_ID Eligible_voters Valid CDU DIELINKE SPD GRÜNE FDP)
drop if Gemeinde_ID=="31-BRIEF"
destring Gemeinde_ID, replace
gen election_typeBWorLW = "LW"
gen Land_code="14"
gen election_date="19Sep2004"
save "${tempdata}S_state_2004.dta", replace

*------------------------------------------------------------------------------*

**Sachsen-Anhalt - 2005 federal elections
import delimited "${rawdata}refromed_counties/votes/Sachsen-Anhalt_federal_2005.csv", delimiter(";") clear
drop ergart-sart b e c-d12
egen int SonstigeParteien = rowtotal(f06-f10)
rename (nr name a f f01 f02 f03 f04 f05)(Gemeinde_ID Gemeinde Eligible_voters Valid SPD CDU DIELINKE FDP GRÜNE)
drop f06-f10
order SonstigeParteien, b(ags2019)
gen election_typeBWorLW = "BW"
gen Land_code="15"
gen election_date="18Sep2005"
drop name2019 ags2019
save "${tempdata}SA_federal_2005.dta", replace

*------------------------------------------------------------------------------* 

***Sachsen - 2005 federal elections
import excel "${rawdata}refromed_counties/votes/Sachsen_federal_2005.xlsx", sheet("14111-091M") clear
drop in 1080/1086
drop in 6/545
drop in 1/4
drop if length(A) < 9
replace A = substr(A, 6,.)
replace A = substr(A, 1,8)
destring A, replace
drop if strpos(A , "-")!=0

foreach x of varlist C-X {
	replace `x' = "0" if `x'=="x"
} // x

foreach x of varlist C-X {
	destring `x', replace
} // x

egen int SonstigeParteien = rowtotal(L-X)
drop D E L-X
destring A, replace
rename (B A C F G H I J K)(Gemeinde Gemeinde_ID Eligible_voters Valid CDU SPD DIELINKE FDP GRÜNE)
gen election_typeBWorLW = "BW"
gen Land_code="14"
gen election_date="18Sep2005"
save "${tempdata}S_federal_2005.dta", replace

*------------------------------------------------------------------------------*

***Sachsen-Anhalt - 2002 state elections
import delimited "${rawdata}refromed_counties/votes/Sachsen-Anhalt_State_2002.csv", delimiter(";") encoding(ISO-8859-2) numericcols(13 14 15 16 17 18 19 20 21) clear
drop wdatum sart b e c-d21
rename (ags name a f f01 f02 f03 f05 f06)(Gemeinde_ID Gemeinde Eligible_voters Valid SPD CDU DIELINKE FDP GRÜNE)
egen int SonstigeParteien = rowtotal(f08-f19)
drop f08-f19
drop name2019
order SonstigeParteien, b(ags2019)
gen election_typeBWorLW = "LW"
gen Land_code="15"
gen election_date="21Apr2002"
drop ags2019
save "${tempdata}SA_state_2002.dta", replace

*------------------------------------------------------------------------------*

***Sachsen-Anhalt - 2006 state elections
import delimited "${rawdata}refromed_counties/votes/Sachsen-Anhalt_State_2006.csv", delimiter(";") clear
rename (ags name f01 f02 f03 f04 f05 a f)(Gemeinde_ID Gemeinde CDU DIELINKE SPD FDP GRÜNE Eligible_voters Valid)
drop ergart-sart b e c-d22
egen int SonstigeParteien = rowtotal(f06-f20)
drop f06-f20
order SonstigeParteien, b(ags2019)
drop name2019 ags2019
gen election_typeBWorLW = "LW"
gen Land_code="15"
gen election_date="26Mar2006"
save "${tempdata}SA_state_2006.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern - 2006 state election
import excel "${rawdata}refromed_counties/votes/Mecklenburg-Vorpommern_state_2006-2.xls", sheet("Ergebnisse nach Gemeinden") clear

drop A B E-G I-Z
drop in 1/6 
drop in 2/3
drop in 930/932
replace C = "Gemeinde_ID" in 1
replace D = "Gemeinde" in 1
replace AD = "DIELINKE" in 1
replace AG = "Offensive_D" in 1
replace AP = "Bündnis_für_M_V" in 1
replace H = "Eligible_voters" in 1
replace AA = "Valid" in 1
foreach x of varlist C-AQ {
  	rename (`x')(`=`x'[1]')
} // x
drop in 1
destring Gemeinde_ID, replace

foreach x of varlist SPD-FAMILIE {
	destring  `x', replace
} // x

egen int SonstigeParteien = rowtotal(Offensive_D NPD GRAUE PBC Deutschland AGFG AB WASG APD Bündnis_für_M_V FAMILIE)

drop Offensive_D NPD GRAUE PBC Deutschland AGFG AB WASG APD Bündnis_für_M_V FAMILIE
drop if substr(Gemeinde,1,5)=="Brief"
gen election_typeBWorLW = "LW"
gen Land_code="13"
gen election_date="17Sep2006"
destring Eligible_voters Valid, replace
save "${tempdata}MV_state_2006.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern - 2009 federal elections
import excel "${rawdata}refromed_counties/votes/Mecklenburg-Vorpommern_federal_2009.xls", sheet("Ergebnisse nach Gemeinden") clear
drop in 1/7
drop in 2/3
drop A B E-G I-AG
drop in 898
replace H = "Eligible_voters" in 1
replace C = "Gemeinde_ID" in 1
replace D = "Gemeinde" in 1
replace AK = "DIELINKE" in 1
replace AH = "Valid" in 1

foreach x of varlist C-AQ {
	rename (`x')(`=`x'[1]')
} // x

drop in 1
destring Gemeinde_ID, replace

foreach x of varlist SPD-PIRATEN {
destring  `x', replace
} // x

egen int SonstigeParteien = rowtotal(NPD MLPD REP PIRATEN)
drop NPD MLPD REP PIRATEN
drop if substr(Gemeinde,1,5)=="Brief"
gen election_typeBWorLW = "BW"
gen Land_code="13"
gen election_date="27Sep2009"
destring Eligible_voters Valid, replace
save "${tempdata}MV_federal_2009.dta", replace

*------------------------------------------------------------------------------*

***Mecklenburg-Vorpommern - 2011 state elections

import excel "${rawdata}refromed_counties/votes/Mecklenburg-Vorpommern_state_2011.xls", sheet("gem") clear
drop A B E-G I-AG
drop in 1/6 
drop in 2/3
replace AW = "DiePARTEI" in 1
replace AU = "FREIEWÄHLER" in 1
replace AK = "DIELINKE" in 1
replace C = "Gemeinde_ID" in 1
replace D = "Gemeinde" in 1
replace H = "Eligible_voters" in 1
replace AH = "Valid" in 1
foreach x of varlist C-AX {
	rename (`x')(`=`x'[1]')
} // x
drop in 1
destring Gemeinde_ID, replace

foreach x of varlist SPD-PIRATEN {
	destring  `x', replace
} // x
egen int SonstigeParteien = rowtotal(NPD FAMILIE PBC AB APD AUF REP FREIEWÄHLER ödp DiePARTEI PIRATEN)
drop NPD FAMILIE PBC AB APD AUF REP FREIEWÄHLER ödp DiePARTEI PIRATEN
drop if substr(Gemeinde,1,5)=="Brief"
gen election_typeBWorLW = "LW"
gen Land_code="13"
gen election_date="4Sep2011"
destring Eligible_voters Valid, replace
save "${tempdata}MV_state_2011.dta", replace

*------------------------------------------------------------------------------*

***Create full dataset
clear

local el " MV_federal_2002 MV_state_2002 S_federal_2002 SA_federal_2002 SA_state_2002 S_state_2004 MV_federal_2005 S_federal_2005 SA_federal_2005 MV_state_2006 SA_state_2006 MV_federal_2009 MV_state_2011 "

foreach e of local el {
	append using "${tempdata}/`e'.dta"
	erase "${tempdata}/`e'.dta"
} // e

drop if Gemeinde_ID==14177370

*------------------------------------------------------------------------------*

***Post-reform counties
merge m:1 Gemeinde_ID using "${tempdata}Sachsen_Anhalt_Gemeinde_to_Different_Kreis.dta", nogen force
erase "${tempdata}Sachsen_Anhalt_Gemeinde_to_Different_Kreis.dta"

replace Gemeinde_ID_=Gemeinde_ID if Gemeinde_ID_==.
drop Gemeinde_ID
rename Gemeinde_ID_ Gemeinde_ID

gen kreis_code=.
gen Kreis=.

tostring Kreis, replace
tostring Gemeinde_ID kreis_code, replace

replace kreis_code = "15089" if substr(Gemeinde_ID,1,5) == "15153"
replace kreis_code = "15001" if substr(Gemeinde_ID,1,5) == "15101"
replace kreis_code = "15001" if substr(Gemeinde_ID,1,5) == "15001"
replace kreis_code = "15002" if substr(Gemeinde_ID,1,5) == "15202"
replace kreis_code = "15003" if substr(Gemeinde_ID,1,5) == "15303"
replace kreis_code = "15081" if substr(Gemeinde_ID,1,5) == "15370"
replace kreis_code = "15082" if substr(Gemeinde_ID,1,5) == "15154"
replace kreis_code = "15082" if substr(Gemeinde_ID,1,5) == "15159"
replace kreis_code = "15083" if substr(Gemeinde_ID,1,5) == "15355"
replace kreis_code = "15083" if substr(Gemeinde_ID,1,5) == "15362"
replace kreis_code = "15084" if substr(Gemeinde_ID,1,5) == "15256"
replace kreis_code = "15084" if substr(Gemeinde_ID,1,5) == "15268"
replace kreis_code = "15085" if substr(Gemeinde_ID,1,5) == "15357"
replace kreis_code = "15085" if substr(Gemeinde_ID,1,5) == "15364"
replace kreis_code = "15085" if substr(Gemeinde_ID,1,5) == "15369"
replace kreis_code = "15086" if substr(Gemeinde_ID,1,5) == "15358"
replace kreis_code = "15087" if substr(Gemeinde_ID,1,5) == "15260"
replace kreis_code = "15087" if substr(Gemeinde_ID,1,5) == "15266"
replace kreis_code = "15088" if substr(Gemeinde_ID,1,5) == "15261"
replace kreis_code = "15088" if substr(Gemeinde_ID,1,5) == "15265"
replace kreis_code = "15089" if substr(Gemeinde_ID,1,5) == "15367"
replace kreis_code = "15090" if substr(Gemeinde_ID,1,5) == "15363"
replace kreis_code = "15091" if substr(Gemeinde_ID,1,5) == "15171"
replace kreis_code = "15082" if substr(Gemeinde_ID,1,5) == "15082"
replace kreis_code = "15085" if substr(Gemeinde_ID,1,5) == "15085"
replace kreis_code = "15086" if substr(Gemeinde_ID,1,5) == "15086"
replace kreis_code = "15089" if substr(Gemeinde_ID,1,5) == "15089"
replace kreis_code = "15091" if substr(Gemeinde_ID,1,5) == "15091"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13001"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13002"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13005"
replace kreis_code = "13074" if substr(Gemeinde_ID,1,5) == "13006"
replace kreis_code = "13072" if substr(Gemeinde_ID,1,5) == "13051"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13052"
replace kreis_code = "13072" if substr(Gemeinde_ID,1,5) == "13053"
replace kreis_code = "13076" if substr(Gemeinde_ID,1,5) == "13054"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13055"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13056"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13057"
replace kreis_code = "13074" if substr(Gemeinde_ID,1,5) == "13058"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13059"
replace kreis_code = "13076" if substr(Gemeinde_ID,1,5) == "13060"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13061"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13062"
replace kreis_code = "13003" if substr(Gemeinde_ID,1,5) == "13003"
replace kreis_code = "13004" if substr(Gemeinde_ID,1,5) == "13004"
replace kreis_code = "13071" if substr(Gemeinde_ID,1,5) == "13071"
replace kreis_code = "13072" if substr(Gemeinde_ID,1,5) == "13072"
replace kreis_code = "13073" if substr(Gemeinde_ID,1,5) == "13073"
replace kreis_code = "13074" if substr(Gemeinde_ID,1,5) == "13074"
replace kreis_code = "13075" if substr(Gemeinde_ID,1,5) == "13075"
replace kreis_code = "13076" if substr(Gemeinde_ID,1,5) == "13076"
replace kreis_code = "14522" if substr(Gemeinde_ID,1,5) == "14177"
replace kreis_code = "14523" if substr(Gemeinde_ID,1,5) == "14178"
replace kreis_code = "14521" if substr(Gemeinde_ID,1,5) == "14181"
replace kreis_code = "14521" if substr(Gemeinde_ID,1,5) == "14191"
replace kreis_code = "14627" if substr(Gemeinde_ID,1,5) == "14280"
replace kreis_code = "14626" if substr(Gemeinde_ID,1,5) == "14284"
replace kreis_code = "14627" if substr(Gemeinde_ID,1,5) == "14285"
replace kreis_code = "14626" if substr(Gemeinde_ID,1,5) == "14286"
replace kreis_code = "14628" if substr(Gemeinde_ID,1,5) == "14287"
replace kreis_code = "14628" if substr(Gemeinde_ID,1,5) == "14290"
replace kreis_code = "14625" if substr(Gemeinde_ID,1,5) == "14292"
replace kreis_code = "14730" if substr(Gemeinde_ID,1,5) == "14374"
replace kreis_code = "14522" if substr(Gemeinde_ID,1,5) == "14375"
replace kreis_code = "14729" if substr(Gemeinde_ID,1,5) == "14379"
replace kreis_code = "14729" if substr(Gemeinde_ID,1,5) == "14383"
replace kreis_code = "14522" if substr(Gemeinde_ID,1,5) == "14182"
replace kreis_code = "14612" if substr(Gemeinde_ID,1,5) == "14262"
replace kreis_code = "14511" if substr(Gemeinde_ID,1,5) == "14161"
replace kreis_code = "14713" if substr(Gemeinde_ID,1,5) == "14365"
replace kreis_code = "14523" if substr(Gemeinde_ID,1,5) == "14166"
replace kreis_code = "14524" if substr(Gemeinde_ID,1,5) == "14167"
replace kreis_code = "14521" if substr(Gemeinde_ID,1,5) == "14171"
replace kreis_code = "14524" if substr(Gemeinde_ID,1,5) == "14173"
replace kreis_code = "14521" if substr(Gemeinde_ID,1,5) == "14188"
replace kreis_code = "14524" if substr(Gemeinde_ID,1,5) == "14193"
replace kreis_code = "14626" if substr(Gemeinde_ID,1,5) == "14263"
replace kreis_code = "14625" if substr(Gemeinde_ID,1,5) == "14264"
replace kreis_code = "14625" if substr(Gemeinde_ID,1,5) == "14272"
replace kreis_code = "14730" if substr(Gemeinde_ID,1,5) == "14389"

*Municipalities joining different counties.
replace kreis_code = "15089" if Gemeinde == "Winningen"
replace kreis_code = "15089" if Gemeinde == "Wilsleben"
replace kreis_code = "15089" if Gemeinde == "Rathmannsdorf"
replace kreis_code = "15082" if Gemeinde == "Bias"
replace kreis_code = "15001" if Gemeinde == "Brambach"
replace kreis_code = "15089" if Gemeinde == "Cochstedt, Stadt"
replace kreis_code = "15086" if Gemeinde == "Dornburg"
replace kreis_code = "15089" if Gemeinde == "Groß Börnecke"
replace kreis_code = "15089" if Gemeinde == "Hohenerxleben"
replace kreis_code = "15091" if Gemeinde == "Zieko"
replace kreis_code = "15082" if Gemeinde == "Luso"
replace kreis_code = "15001" if Gemeinde == "Rodleben"
replace kreis_code = "15089" if Gemeinde == "Schneidlingen"
replace kreis_code = "15001" if Gemeinde == "Mühlstedt"
replace kreis_code = "15089" if Gemeinde == "Löderburg"
replace kreis_code = "15086" if Gemeinde == "Leitzkau"
replace kreis_code = "15086" if Gemeinde == "Ladeburg"
replace kreis_code = "15089" if Gemeinde == "Klein Schierstedt"

destring Gemeinde_ID kreis_code, replace

replace Kreis = "Altmarkkreis Salzwedel" if kreis_code==15081
replace Kreis = "Anhalt-Bitterfeld, Landkreis" if kreis_code==15082
replace Kreis = "Börde, Landkreis" if kreis_code==15083
replace Kreis= "Burgenlandkreis" if kreis_code==15084
replace Kreis= "Harz, Landkreis"  if kreis_code==15085
replace Kreis = "Jerichower Land, Landkreis" if kreis_code==15086
replace Kreis = "Mansfeld-Südharz, Landkreis" if kreis_code==15087
replace Kreis = "Saalekreis" if kreis_code==15088
replace Kreis = "Salzlandkreis" if kreis_code==15089
replace Kreis= "Stendal, Landkreis" if kreis_code==15090
replace Kreis= "Wittenberg, Landkreis"  if kreis_code==15091
replace Kreis= "Dessau-Roßlau, Kreisfreie Stadt"  if kreis_code==15001
replace Kreis= "Halle (Saale), Kreisfreie Stadt"  if kreis_code==15002
replace Kreis= "Magdeburg, Kreisfreie Stadt"  if kreis_code==15003

replace Kreis = "Landkreis Mecklenburgische Seenplatte" if kreis_code==13071
replace Kreis = "Landkreis Rostock" if kreis_code==13072
replace Kreis = "Landkreis Vorpommern-Rügen" if kreis_code==13073
replace Kreis = "Landkreis Nordwestmecklenburg" if kreis_code==13074
replace Kreis= "Landkreis Vorpommern-Greifswald" if kreis_code==13075
replace Kreis= "Landkreis Ludwigslust-Parchim"  if kreis_code==13076
replace Kreis= "Kreisfreie Stadt Rostock, Hansestadt" if kreis_code==13003
replace Kreis= "Kreisfreie Stadt Schwerin, Landeshauptstadt"  if kreis_code==13004

replace Kreis = "Erzgebirgskreis" if kreis_code==14521
replace Kreis = "Mittelsachsen, Landkreis" if kreis_code==14522
replace Kreis = "Vogtlandkreis" if kreis_code==14523
replace Kreis= "Zwickau, Landkreis" if kreis_code==14524
replace Kreis= "Bautzen, Landkreis"  if kreis_code==14625
replace Kreis= "Dresden, Stadt"  if kreis_code==14612
replace Kreis= "Chemnitz, Stadt"  if kreis_code==14511
replace Kreis = "Görlitz, Landkreis" if kreis_code==14626
replace Kreis = "Meißen, Landkreis" if kreis_code==14627
replace Kreis = "Sächsische Schweiz-Osterzgebirge, Landkreis" if kreis_code==14628
replace Kreis = "Leipzig, Landkreis" if kreis_code==14729
replace Kreis= "Nordsachsen, Landkreis" if kreis_code==14730
replace Kreis = "Leipzig, Kreisfreie Stadt" if kreis_code==14713
replace Land_code="15" if kreis_code==15089

collapse (sum) Eligible_voters-SonstigeParteien, by(kreis_code Kreis Land_code election_date election_typeBWorLW)
gen election_date2 = date(election_date, "DMY")
format election_date2 %td
drop election_date
rename election_date2 election_date
destring Land_code, replace
order kreis_code Kreis Land_code election_typeBWorLW election_date
sort kreis_code election_date
gen turnout = Valid / Eligible_voters

rename GRÜNE GRUNE
gen Land = .
tostring Land, replace
replace Land = "Mecklenburg_Vorpommern" if Land_code==13
replace Land = "Sachsen" if Land_code==14
replace Land = "Sachsen_Anhalt" if Land_code==15
order Land, b(Land_code)
order turnout, b(SPD)
drop if kreis_code == .
sort kreis_code election_date election_typeBWorLW

replace Land = subinstr(Land, "_", "-", .)

*Save file with total votes
save "${tempdata}MV_S_SA_reformed_counties_voting.dta", replace

*------------------------------------------------------------------------------*

clear

exit
